<%@page language="java" contentType="text/javascript; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%
	
	Connection conn = null;
	PreparedStatement pstmt = null;
    
    String p = request.getParameter("pages"); 
    String rpp = request.getParameter("rpp");
    String start = request.getParameter("start");
    int rpps = Integer.parseInt(rpp);
    int starts = Integer.parseInt(start);
    
    JSONObject jo = new JSONObject();
    JSONObject jo2 = new JSONObject();
    JSONArray ja = new JSONArray();
    
    
    try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
	
		
		String select = "select * from Post where Continent is null order by Date desc, Time desc limit ?, ?;";
		pstmt = conn.prepareStatement(select);
		pstmt.setInt(1, starts);
		pstmt.setInt(2, rpps);
		
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()){
			String num = rs.getString("Number");
			String id = rs.getString("ID");
			String writer = rs.getString("Writer");
			String title = rs.getString("Title");
			String content = rs.getString("Content");
			if (title != null) {
				title = title.replace("\n","<br>");
				title = title.replace("\u0020","&nbsp;");
			}
			if (content != null) {
				content = content.replace("\n","<br>");
				content = content.replace("\u0020","&nbsp;");
			}
			
			String date = rs.getString("Date");
			String MMdd = date.substring(5);
			String time = rs.getString("Time");
			String hhmm = time.substring(0, 5);

			jo.put("Number", num);
			jo.put("ID", id);
			jo.put("WRITER", writer);
			jo.put("TITLE", title);
			jo.put("CONTENT", content);
			jo.put("DATE", date);
			jo.put("MMDD", MMdd);
			jo.put("TIME", time);
			jo.put("HHMM", hhmm);
			ja.add(jo);
			jo2.put("results", ja);
			}
		
		String select2 = "select count(*) Total from Post where Continent is null";
		pstmt = conn.prepareStatement(select2);
		
		ResultSet rs2 = pstmt.executeQuery();
		
		while(rs2.next()){
			int total = rs2.getInt("Total");
			jo2.put("total", new Integer(total));
		}
		out.print(jo2);
    }
    
	finally {
        try { 
            pstmt.close();
        } 
        catch (Exception ignored) {
        }
        try { 
            conn.close();
        } 
        catch (Exception ignored) {
        }
    }
%>);